SnowflakeとSaaS間のデータ連携を容易に実現！Omnataが提供するNative Apps「Omnata Google Sheets Plugin」を使ってGoogleスプレッドシートとSnowflake間のデータ連携を試してみた

さがらです。

Omnataという、SnowflakeにNative Appsの形で特化した、各SaaSとのデータ連携に使用できるコネクタを提供している企業があります。

https://omnata.com/

今回、Snowflake Marketplaceで公開・販売されているNative AppsであるOmnata Google Sheets Pluginを用いて、「GoogleスプレッドシートからSnowflakeへのデータロード」と「SnowflakeからGoogleスプレッドシートへのReverse ETL」を行ってみたので、本記事でまとめてみます。

https://docs.omnata.com/omnata-product-documentation/omnata-sync-for-snowflake/apps/google-sheets

Omnata Sync Engineのインストール

まず、どのOmnataのPlugin(コネクタ)を使うにあたっても、Omnata Sync Engineが必要となります。

Marketplaceで「Omnata Sync Engine」と検索するとヒットするため、これをインストールします。

インストール後のアプリケーションの名前と、使用するウェアハウスを選択します。ACCOUNTADMINの権限が必要となるためご注意ください。

下図のようにポップアップが表示されます。私が行った際は約5分後、インストールされました。

インストール後下図の画面となるので、Configureを押します。

Streamlitのアプリケーションが立ち上がります。EXECUTE TASKの権限を求められるため、Grant Privilegesを押します。

ログをOmnata社に共有してもよいか表示されるため、お好みのオプションを選択します。

日次の管理用タスクのために、ウェアハウスの権限を与える必要があります。別途ワークシートを開いて、表示されたクエリを編集し、実行します。実行後はOmnataのアプリ上でCheckを押し、使用するウェアハウスを選択してSaveを押します。

下図の画面が出てくれば、Omnata Sync Engineの初期セットアップは完了です！

Omnata Google Sheets Pluginのインストール

続いて、Googleスプレッドシート用のPluginをインストールします。Googleスプレッドシート用のPluginは無料で提供されているのが嬉しいですね！

Marketplaceで「Omnata Google」と検索するとヒットするため、これをインストールします。

インストール後のアプリケーションの名前と、使用するウェアハウスを選択します。ACCOUNTADMINの権限が必要となるためご注意ください。

下図のようにポップアップが表示されます。私が行った際は約1分後、インストールされました。

インストール後下図の画面となるので、Openを押します。

Streamlitのアプリケーションが立ち上がりPluginのConfigurationが始まります。

Omnata Sync Engineをインストールしているか聞かれるため、Yesを押します。

Omnata Sync Engineのアプリケーションに対してGoogle Sheets Pluginのアプリケーションの権限を付与する必要があります。

Omnata App nameを修正してEnterを押すと下部のクエリもその名前で更新されますので、別途ワークシートを開いて生成されたクエリを実行します。

Google Sheets PluginのStreamlitのアプリケーションの画面に戻り、Show next stepを押します。

続いて、Pluginに対してapplication roleを付与するため、表示されたクエリをワークシートで実行します。

Google Sheets PluginのStreamlitのアプリケーションの画面に戻り、Show next stepを押します。

下図のように表示されれば、Google Sheets Pluginのセットアップは完了です！

Google SheetsのConnectionのセットアップ

次に、OmnataとGoogle Sheets間の認証を行うためにConnectionのセットアップを行っていきます。

事前準備：サービスアカウントの作成

後述するセットアップではOmnataとGoogle Sheetsの認証でサービスアカウントを使用するため、Google側でサービスアカウントの作成を行います。（Google Cloudプロジェクトが必要となる点だけご注意ください。）

Google CloudのIAMと管理のサービスアカウントから、サービスアカウントを作成を押します。

サービスアカウント名などを入力し、作成して続行を押します。

2番目と3番目のステップはスキップして問題ないため、このまま完了を押します。

作成されたサービスアカウントの詳細画面に移動し、キータブから鍵を追加→新しい鍵を作成を押します。キーのタイプはJSONで作成を押します。

下図のように表示されJSONファイルがダウンロードされます。あとでOmnataでの設定で使用しますので、大切に保存しておきましょう。

次に対象のサービスアカウントのアドレスに対して、Omnataで連携対象のドライブに対して編集者権限を付与します。これで、サービスアカウントの準備は完了です。

事前準備：各種APIの有効化

OmnataはGoogle Drive APIとGoogle Sheets APIを使用しているため、サービスアカウントを作成したGoogle Cloudプロジェクトで有効化しておきます。

Google SheetsのConnectionのセットアップ

Omnata Sync Engineのアプリケーションを起動し、左のConnectionsを押します。

Create your first connectionを押します。

Google Sheetsが表示されているため、Selectを押します。

新しいConnectionの設定を行う画面が表示されますので、それぞれ入力していきます。Connection MethodはOAuthとService Accountが選択可能ですが、今回は事前準備もしていたのでService Accountにします。

EXTERNAL ACCESS INTEGRATIONに関するクエリが表示されるため、コピーしてワークシートに移動し、ACCOUNTADMINで実行します。クエリ実行後、末尾のCheckを押します。

事前に作成したサービスアカウントのJSONキーの内容をコピーし、Service Account JSONに貼り付けます。その後、Connectを押します。

Connectionの設定のために許可が必要なアドレス一覧が表示されます。問題なければConfirmを押します。

数十秒後、設定が完了したら下図のように表示されます。これでConnectionの設定は完了です。

Googleスプレッドシート→Snowflakeのデータロード

準備は整ったので、実際にGoogleスプレッドシート→Snowflakeのデータ連携をやってみます。

検証内容

jaffle_shopというファイルを用意し、customersとordersというシートを用意しておきます。これらのデータをSnowflakeにロードすることを行ってみます。

Sync設定手順

Omnataの画面で、左のメニューからNew Syncを押します。

Get startedを押します。

Google Sheetsの下部のSelectを押します。

Select Connectionで、先程作成したConnectionを選択して、Continueを押します。

次にsync directionを選択します。GoogleスプレッドシートからSnowflakeへのロードのため、Inboundを選択します。

Select Objectsが表示されるため、Spreadsheet IDから同期したいスプレッドシートであるjaffle_shopを選択します。

連携可能なシートの一覧が表示されるため、連携したいシートの左横をチェックします。その後、Continueを押します。

（今回は検証しませんが、Set new object behaviourをYesにすると、新しいシートが追加されたときにも自動で追加してくれるようです。）

Set the sync strategyが表示されます。公式Docによると、Google Sheetsの場合はFull Refreshしかできない仕様となっています。

画面の左側に、各オプションの説明も記載されています。今回はmergeで進めます。（画面左の説明と合致していないですが、mergeとappendが選択できるのが正しいようです。左側の表記については次回のアップデートで修正されるとのこと。）

mergeの場合、各Objectに対してID Field(s)を入力し、Continueを押します。

Choose deployment methodが表示されます。スケジュールや使用するウェアハウスを設定する画面となっています。Dependentでは、別のSyncに併せて実行するということも可能のようです。

設定が確認できたら、Start Syncを押します。

下図のように表示されたら、Syncの設定は完了です！

View Syncから作成したSyncを見ると、Snowflakeの出力先となるデータベース・スキーマ・テーブルも定義されていました。（ここはOmnataが自動で指定した内容で定義されるようです。）

手動でSync実行してみる

作成したSyncを用いて、手動でSyncを実行してみたいと思います。

Omnataの画面で、左のメニューからSyncsを押し、先ほど作成したSyncの右横のViewを選択します。

Controlsタブから、Run nowを押します。

Historyタブを見ると、新しくSyncが開始されていることがわかります。

無事に完了すると、Health StateがHEALTHYとなります。Viewを押すと、どのテーブルが何レコードロードされたのかも見ることが出来ます。

実際にデータを見てみると、INBOUND_RAWというスキーマには抽出したJSON形でそのままロードされたテーブルが作られており、INBOUND_NORMALIZEDというスキーマにはINBOUND_RAWで作られたテーブルからJSON内の値を抽出してカラムとして追加したビューが作られています。

Snowflake⇢GoogleスプレッドシートのReverseETL

続いて、OmnataではDWH⇢各アプリケーションのReverseETLもできるため、SnowflakeからGoogleスプレッドシートへのReverseETLを行ってみます。

検証内容

事前に下図のようなスプレッドシートを用意しておき、このスプレッドシートにCUSTOMERSというSnowflake上でdbtを介して加工処理を行ったテーブルの内容をエクスポートしてみます。

Sync設定手順

まず画面左のNew Syncを押し、Get startedを押します。

Google Sheetsの下にあるSelectを押します。

事前に作成済のConnectionを選択し、Continueを押します。

Snowflakeから外部への連携のため、Outboundを押します。

すると、今回ReverseETLの対象であるデータベース・スキーマ・テーブルへの権限の付与を求められるため、表示されたクエリの内容を対象のデータベース・スキーマ・テーブルに変更し、ワークシートで実行します。

権限の付与を行ったあと、Omnataの画面に戻りRefreshを押すとリストから対象のデータベース・スキーマ・テーブルが選択できるため、選択します。ここでは、IDに該当するカラムも選択します。

その後、選択したIDに該当するカラムが重複なしであることが確認できたら、Continuteを押します。

Set a sync strategyの画面になるので、Replaceを選択します。

Spreadsheed IDでReverseETLの宛先となるスプレッドシート、Sheet Nameを選択します。

その後、各オプションは必要なところにチェックを入れて、Continueを押します。

INBOUNDの設定のときと同様にスケジュールの設定画面となりますので、任意のスケジュール設定を行い、Start Syncを押します。

これでReverseETLのSyncの設定は完了です！

手動でSync実行してみる

作成したSyncを用いて、手動でSyncを実行してみたいと思います。

Omnataの画面で、左のメニューからSyncsを押し、先ほど作成したSyncの右横のViewを選択します。

Controlsタブから、Run nowを押します。

一定時間経過後、Historyタブを見るとRunが無事に完了していました。Viewから対象のRunの詳細も確認できます。

この上で、宛先に設定していたスプレッドシートを見ると、無事にSyncが出来ていました！

おまけ：Omnataの裏側の仕組み

OmnataはStreamlitで動作するアプリケーションですが、バックエンドとして多くのUDFやストアドプロシージャが使われています。

試しにREAD_SHEETというUDFを使ってみると、対象のスプレッドシートのデータを得ることが出来ます。

最後に

Omnataが提供するNative Apps「Omnata Google Sheets Plugin」を使ってGoogleスプレッドシートとSnowflake間のデータ連携を試してみました。
Omnataだけで、GoogleスプレッドシートとSnowflake双方向の同期ができるのはとても便利だと感じました！

これは余談なのですが、Omnataの検証を始めたのは６月中旬で、INBOUNDのSyncがうまくいかない事象が発生して数十回ほどやりとりをOmnata社の方とさせて頂き、最終的に無事にSyncができるようになったという背景があります。事象が解決するまで何度も解決策を提示して頂いたりアプリケーションのアップデートも行って頂いたことには感謝しかありません！Omnata社の対応いただいた皆様、ありがとうございました！